﻿/*
 XsltDb is powerful XSLT module for DotnetNuke.
 It offers safe database access, SEO-friendly AJAX support,
 visitor interactions, environment integration (dnn properties,
 request, cookie, session and form values), regular expressions, etc.

 Author:
 
    Anton Burtsev
    burtsev@yandex.ru

 Project home page: 
 
    http://xsltdb.codeplex.com
*/

using System;
using System.Web.Caching;
using System.Web;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using System.IO;
using System.Xml;
using System.Xml.XPath;
using System.Data;
using System.Data.SqlClient;
using System.Security.Cryptography;

using DotNetNuke.Entities.Modules;
using DotNetNuke.Common;
using DotNetNuke.Data;
using DotNetNuke.Services.Search;
using DotNetNuke.Common.Utilities;
using DotNetNuke.Entities.Users;

namespace Findy.XsltDb
{

    /// <summary>
    /// Summary description for XsltDbControler
    /// </summary>
    public class XsltDbController : IPortable, ISearchable, IUpgradeable
    {
        #region IPortable Members

        public string ExportModule(int ModuleID)
        {
            return XsltDbUtils.GetXSLTData(ModuleID);
        }

        public void ImportModule(int ModuleID, string Content, string Version, int UserID)
        {
            XmlNode content = Globals.GetContent(Content, "xsltdb");
            XsltDbUtils.ImportModule(ModuleID, content.OuterXml);
        }

        #endregion

        public SearchItemInfoCollection GetSearchItems(ModuleInfo ModInfo)
        {
            try
            {
                Transformer t = new Transformer(ModInfo.TabModuleID, ModInfo.ModuleID, ModInfo.TabID, ModInfo.PortalID);
                XsltDbUtils.aConfig config = XsltDbUtils.GetConfig(ModInfo.ModuleID, ModInfo.TabID);
                t.Config = config;
                if (config != null)
                {
                    string xml = t.ExecuteSearch(config.ActiveXslt, config.IsSuper);
                    XPathDocument doc = new XPathDocument(new StringReader(xml));
                    SearchItemInfoCollection items = new SearchItemInfoCollection();
                    foreach (XPathNavigator nav in doc.CreateNavigator().Select("//search-item"))
                    {
                        try
                        {
                            SearchItemInfo si = new SearchItemInfo(
                                nav.Value("title"),
                                nav.Value("description"),
                                int.Parse(nav.Value("user", Null.NullInteger.ToString())),
                                DateTime.Parse(nav.Value("date", DateTime.Now.ToString())),
                                int.Parse(nav.Value("module", ModInfo.ModuleID.ToString())),
                                nav.Value("key"),
                                nav.Value("content"),
                                nav.Value("guid"),
                                int.Parse(nav.Value("image", Null.NullInteger.ToString()))
                                );

                            items.Add(si);
                        }
                        catch (Exception) { }
                    }
                    return items;
                }
            }
            catch (Exception) { }
            return null;
        }

        public string UpgradeModule(string Version)
        {
            string path = HttpContext.Current.Server.MapPath("~/DesktopModules/XsltDb/ws.aspx.cs");
            DeleteFile(path);
            path = HttpContext.Current.Server.MapPath("~/DesktopModules/XsltDb/ajax.aspx");
            DeleteFile(path);
            DeleteFile(path + ".cs");
            return "Success";
        }
        private static void DeleteFile(string file)
        {
            for (int i = 0; i < 5; i++)
            {
                try
                {
                    if (File.Exists(file))
                        File.Delete(file);
                    return;
                }
                catch
                {
                    System.Threading.Thread.Sleep(100);
                }
            }
        }
    }

    public static class XsltDbExtensions
    {
        public static string Value(this XPathNavigator nav, string xpath)
        {
            return Value(nav, xpath, string.Empty);
        }
        public static string Value(this XPathNavigator nav, string xpath, string def)
        {
            XPathNavigator val = nav.SelectSingleNode(xpath);
            if (val == null)
                return def;
            return string.IsNullOrEmpty(val.Value) ? def : val.Value;
        }

        public static string MD5(this string source)
        {
            return Encoding.UTF8.GetBytes(source).MD5();
        }
        public static string MD5(this byte[] source)
        {
            return BitConverter.ToString(
                new MD5CryptoServiceProvider().ComputeHash(source)).ToLower().Replace("-", "");
        }
        public static ushort Crc16(this string source)
        {
            byte[] b = new MD5CryptoServiceProvider().ComputeHash(Encoding.Unicode.GetBytes(source));
            return (ushort)(((int)b[0]) * ((int)b[1]));
        }

        public static string xJson(this object source)
        {
            return new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(source);
        }
        public static T fJson<T>(this string json)
        {
            return new System.Web.Script.Serialization.JavaScriptSerializer().Deserialize<T>(json);
        }
    }

    public class Data
    {
        public static void ExecuteSQLCommand(string SQL, SqlParameter[] parameters)
        {
            using (var reader = ExecuteSQL(SQL, parameters))
            {
                reader.Read();
                reader.Close();
            }
        }

        public static IDataReader ExecuteSQL(string SQL, SqlParameter[] parameters)
        {
            return ExecuteSQL(SQL, true, parameters);
        }

        public static object ExecuteSQLScalar(string SQL, SqlParameter[] parameters)
        {
            using ( var reader = ExecuteSQL(SQL, true, parameters) )
            {
                if (reader.Read())
                    return reader[0];
                reader.Close();
            }
            return null;
        }

        public static IDataReader ExecuteSQL(string SQL, Helper.CacheState cacheState, string connectionString, SqlParameter[] parameters)
        {
            return ExecuteSQL(SQL, connectionString == null, cacheState, connectionString, parameters);
        }

        public static XPathNavigator ExecuteSQL(string SQL, string names, SqlParameter[] parameters)
        {
            using (IDataReader r = ExecuteSQL(SQL, parameters))
                return Helper.GetXmlFromReader(r, names);
        }
        private static IDataReader ExecuteSQL(string SQL, bool prepare, Helper.CacheState cacheState, string connectionString, SqlParameter[] parameters)
        {
            Dictionary<string, string> logItems = new Dictionary<string, string>();
            try
            {
                if (prepare) SQL = PrepareSQL(SQL);
                logItems["SQL"] = SQL;
                SqlConnection cnn = new SqlConnection(connectionString ?? DataProvider.Instance().ConnectionString);
                cnn.Open();
                SqlCommand cmd = new SqlCommand(SQL, cnn);
                if (parameters != null && parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                    foreach (var p in parameters)
                        logItems[p.ParameterName] = p.Value.ToString();
                }
                if (cacheState == null || cacheState.selfCache == false || HttpContext.Current == null)
                    return cmd.ExecuteReader(CommandBehavior.CloseConnection);

                EnsureServiceBrokerListener(cnn.ConnectionString);

                cacheState.dep = new SqlCacheDependency(cmd);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            finally { 
                if ( string.Compare(
                    UserController.GetCurrentUserInfo().Profile.GetPropertyValue("XsltDbDebugger"),
                    "True", true ) == 0)
                      XsltDbUtils.WriteLog(logItems);
            }
        }

        private static IDataReader ExecuteSQL(string SQL, bool prepare, SqlParameter[] parameters)
        {
            return ExecuteSQL(SQL, prepare, null, null, parameters);
        }

        public static XPathNavigator SQL2XML(string SQL, Helper.CacheState cacheState, SqlParameter[] parameters)
        {
            return SQL2XML(SQL, null, cacheState, parameters);
        }
        public static XPathNavigator SQL2XML(string SQL, string connectionString, Helper.CacheState cacheState, SqlParameter[] parameters)
        {
            if (connectionString == null )
                SQL = PrepareSQL(SQL);

            using (SqlConnection cnn = new SqlConnection(connectionString ?? DataProvider.Instance().ConnectionString))
            {
                cnn.Open();
                SqlCommand cmd = new SqlCommand(SQL, cnn);
                if (parameters != null && parameters.Length > 0)
                    cmd.Parameters.AddRange(parameters);
                if (cacheState == null || cacheState.selfCache == false || HttpContext.Current == null)
                    return new XPathDocument(cmd.ExecuteXmlReader()).CreateNavigator();

                EnsureServiceBrokerListener(cnn.ConnectionString);

                cacheState.dep = new SqlCacheDependency(cmd);
                return new XPathDocument(cmd.ExecuteXmlReader()).CreateNavigator();
            }
        }

        public static XPathNavigator SP2XML(string procedure, Helper.CacheState cacheState, object[] parameters)
        {
            return SQL2XML(BuildSQL(procedure, parameters), cacheState, null);
        }

        private static string PrepareSQL(string SQL, string dbo, string qua)
        {
            return SQL.Replace("{databaseOwner}", dbo).Replace("{objectQualifier}", qua);
        }
        public static string PrepareSQL(string SQL)
        {
            return PrepareSQL(SQL,
                DataProvider.Instance().DatabaseOwner,
                DataProvider.Instance().ObjectQualifier
                );
        }

        private static string BuildSQL(string procedure, object[] parameters)
        {
            procedure = DataProvider.Instance().ObjectQualifier + procedure;
            bool wasChecked = false;
            string cacheKey = "xsltdb:procedures:" + procedure;
            bool valid = (bool)StaticCache2.Get(cacheKey, delegate() {
                wasChecked = true;
                string check = "select * from ( select object_id(@proc) obj_id ) t where obj_id is not null;";
                using (IDataReader r = ExecuteSQL(check, new SqlParameter[]{new SqlParameter("@proc", procedure)}))
                    return r.Read();
            });

            if (valid || !wasChecked)
            {
                procedure = DataProvider.Instance().DatabaseOwner + procedure;
                string sql = "exec " + procedure;
                if (parameters != null && parameters.Length > 0)
                    foreach (object value in parameters)
                        sql += " '" + value.ToString().Replace("'", "''") + "',";
                if (sql.EndsWith(","))
                    sql = sql.Substring(0, sql.Length - 1);
                return sql;
            }
            else
                throw new Exception("Procedure '" + procedure + "' can't be found.");
        }
        public static IDataReader ExecuteReader(string procedure, Helper.CacheState cacheState, object[] parameters)
        {
            return ExecuteSQL(BuildSQL(procedure, parameters), false, cacheState, null, null);
        }

        public static object ExecuteScalar(string procedure, Helper.CacheState cacheState, object[] parameters)
        {
            using (IDataReader r = ExecuteReader(procedure, cacheState, parameters))
                if (r.Read())
                    return r[0];
            return string.Empty;
        }

        public static void ExecuteNonQuery(string procedure, object[] parameters)
        {
            using (IDataReader r = ExecuteReader(procedure, null, parameters))
                return;
        }

        public static CacheDependency CreateDependency(string[] tables, string connectionString, SqlParameter[] parameters)
        {
            if (tables.Length == 1)
                return CreateDependency(tables[0], connectionString, parameters);

            AggregateCacheDependency aggdep = new AggregateCacheDependency();
            foreach ( string table in tables )
                aggdep.Add(CreateDependency(table, connectionString, parameters));
            return aggdep;
        }
        private static string GetConnectionByName(string dbName)
        {
            XPathDocument doc = new XPathDocument(System.Web.HttpContext.Current.Server.MapPath("~/web.config"));

            var db = doc.CreateNavigator().SelectSingleNode(
                "/configuration/system.web/caching/sqlCacheDependency/databases/add[@name='" +
                dbName + "']/@connectionStringName");
            if ( db != null )
                return System.Web.Configuration.WebConfigurationManager.ConnectionStrings[db.Value].ConnectionString;
            return null;
        }
        static Regex queryRx = new Regex(@"^(?<table>.*?)\s*(?<br>\((?<sql>.*?)\))?$", RegexOptions.Singleline | RegexOptions.Compiled);
        public static SqlCacheDependency CreateDependency(string table, string connectionString, SqlParameter[] parameters)
        {
            string connStr = connectionString ?? DataProvider.Instance().ConnectionString;
            string dbo = connectionString != null ? "dbo." : DataProvider.Instance().DatabaseOwner;
            string pre = connectionString != null ? "" : DataProvider.Instance().ObjectQualifier;

            Match m = queryRx.Match(table);

            string tableName = PrepareSQL(m.Groups["table"].Value, dbo, pre).Trim();
            string sql = PrepareSQL(m.Groups["sql"].Value, dbo, pre).Trim();

            if ( tableName == "$query" )
                return CreateNotificationDependence(sql, connStr, parameters);
            else if ( m.Groups["br"].Success )
            {
                if ( !tableName.Contains(".") )
                    tableName = dbo + tableName;
                string query = "select null from " + tableName;
                if (sql.Length > 0)
                    query += " where " + sql;
                return CreateNotificationDependence(query, connStr, parameters);
            }

            string cnnName = "XsltDbCache";
            if (connectionString != null)
                cnnName +="_" + DotNetNuke.Entities.Portals.PortalController.GetCurrentPortalSettings().PortalId;

            try
            {
                return new SqlCacheDependency(cnnName, table);
            }
            catch (DatabaseNotEnabledForNotificationException)
            {
                string cnn = GetConnectionByName(cnnName);
                SqlCacheDependencyAdmin.EnableNotifications(cnn);
                SqlCacheDependencyAdmin.EnableTableForNotifications(cnn, table);
            }
            catch (TableNotEnabledForNotificationException)
            {
                string cnn = GetConnectionByName(cnnName);
                SqlCacheDependencyAdmin.EnableTableForNotifications(cnn, table);
            }
            return new SqlCacheDependency(cnnName, table);
        }

        private static SqlCacheDependency CreateNotificationDependence(string query, string connectionString, SqlParameter[] parameters)
        {
            EnsureServiceBrokerListener(connectionString);
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = query;
                query = query.ToLower();
                foreach (SqlParameter p in parameters)
                    if (query.Contains(p.ParameterName.ToLower()))
                        cmd.Parameters.Add(p);
                SqlCacheDependency dep = new SqlCacheDependency(cmd);
                cmd.ExecuteNonQuery();
                return dep;
            }

        }

        private static Dictionary<string, bool> ListeningConnections = new Dictionary<string, bool>();

        private static void EnsureServiceBrokerListener(string conn)
        {
            if ( SqlDependency.Start(conn) )
            {
                ListeningConnections[conn] = true;
                HttpContext.Current.ApplicationInstance.Disposed += new EventHandler(ApplicationInstance_Disposed);
            }
        }

        private static void ApplicationInstance_Disposed(object sender, EventArgs e)
        {
            lock (typeof(Data))
            {
                foreach (string c in ListeningConnections.Keys)
                {
                    try
                    {
                        SqlDependency.Stop(c);
                    }
                    catch { }
                }

                ListeningConnections.Clear();
            }
        }
    }

    public class JsonHelpers
    {
        public static Dictionary<string, object>[] ConvertDataTableToDic(DataTable dt)
        {
            Dictionary<string, object>[] rows = new Dictionary<string, object>[dt.Rows.Count];

            for (int i = 0; i < rows.Length; i++)
            {
                rows[i] = new Dictionary<string, object>();
                foreach (DataColumn dc in dt.Columns)
                    rows[i][dc.ColumnName] = dt.Rows[i][dc.ColumnName];
            }
            return rows;
        }

        public static Dictionary<string, object>[] ConvertDataSetToDic(DataSet ds)
        {
            if (ds.Tables.Count == 1)
                return ConvertDataTableToDic(ds.Tables[0]);

            Dictionary<string, object>[] rows = new Dictionary<string, object>[ds.Tables.Count];

            for (int i = 0; i < ds.Tables.Count; i++)
            {
                rows[i] = new Dictionary<string, object>();
                rows[i][ds.Tables[i].TableName] = ConvertDataTableToDic(ds.Tables[i]);
            }

            return rows;
        }

        internal static XPathNavigator Json2Xml(string text)
        {
            StringWriter sw = new StringWriter();
            XmlWriterSettings s = new System.Xml.XmlWriterSettings();
            s.OmitXmlDeclaration = true;
            XmlWriter xw = XmlWriter.Create(sw,s);
            xw.WriteStartElement("root");
            ProcessObject(text.fJson<object>(), xw, null);
            xw.Close();

            return new XPathDocument(new StringReader(sw.ToString())).CreateNavigator();
        }

        static void ProcessObject(object o, XmlWriter xw, string name)
        {
            name = XmlConvert.EncodeLocalName(name ?? "row");

            if (!(o is object[]))
                xw.WriteStartElement(name);

            if (o is IDictionary<string, object>)
                foreach (var r in o as IDictionary<string, object>)
                    ProcessObject(r.Value, xw, r.Key);
            else if ( o is object[] )
                foreach (object co in (object[])o)
                    ProcessObject(co, xw, name);
            else
                xw.WriteValue(o.ToString());

            if (!(o is object[]))
                xw.WriteEndElement();
        }
    }

    
    public interface IDTP
    {
        string Value { get; set; }
        bool AutoPostBack { get; set; }
    }

    public interface IXsltDbService { }
    public interface IXsltDbAjax { }

    public interface IXsltDbHost
    {
        string XContext { get; set; }
    }
}

namespace Findy.XsltDb.Logging
{
    public enum EventLogType
    {
        XSLTDB_DATABASE_ACCESS
    }
}